using System.Data;
using System.Diagnostics;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;
using MySql.Data.MySqlClient;
using Npgsql;
using SPI.AgentServer.Configuration;

namespace SPI.AgentServer.Services.Execution;

public class DatabaseExecutor : IDatabaseExecutor
{
    private readonly DatabaseConfiguration _config;
    private readonly ILogger<DatabaseExecutor> _logger;

    public DatabaseExecutor(IOptions<DatabaseConfiguration> options, ILogger<DatabaseExecutor> logger)
    {
        _config = options.Value;
        _logger = logger;
    }

    public async Task<QueryExecutionResult> ExecuteQueryAsync(string sql, string? databaseName = null, CancellationToken cancellationToken = default)
    {
        var stopwatch = Stopwatch.StartNew();
        var result = new QueryExecutionResult();
        
        const int maxRetries = 3;
        Exception? lastException = null;

        for (int attempt = 1; attempt <= maxRetries; attempt++)
        {
            try
            {
                if (attempt > 1)
                {
                    _logger.LogWarning("重试数据库查询，第 {Attempt} 次尝试", attempt);
                    await Task.Delay(TimeSpan.FromSeconds(Math.Pow(2, attempt - 1)), cancellationToken); // 指数退避
                }

                var connection = GetConnectionInfo(databaseName);
                using var conn = CreateConnection(connection);
                
                // 连接超时已在连接字符串中配置
                conn.Open();
                
                using var cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandTimeout = _config.QueryTimeout;

                using var reader = cmd.ExecuteReader();
                
                // Read data into dynamic list
                var data = new List<Dictionary<string, object>>();
                var columns = new List<string>();

                // Get column names
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    columns.Add(reader.GetName(i));
                }

                // Read rows
                while (reader.Read() && data.Count < _config.MaxResults)
                {
                    var row = new Dictionary<string, object>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        row[columns[i]] = reader.IsDBNull(i) ? null : reader.GetValue(i);
                    }
                    data.Add(row);
                }

                result.Success = true;
                result.Data = new
                {
                    columns = columns,
                    rows = data,
                    hasMore = data.Count >= _config.MaxResults
                };
                result.RowCount = data.Count;
                result.ExecutionTimeMs = stopwatch.ElapsedMilliseconds;

                _logger.LogInformation("✅ Executed query on {Database} in {Time}ms, returned {Rows} rows{Retry}", 
                    connection.Name, result.ExecutionTimeMs, result.RowCount,
                    attempt > 1 ? $" (重试 {attempt - 1} 次后成功)" : "");

                return result;
            }
            catch (Exception ex)
            {
                lastException = ex;
                
                if (attempt < maxRetries)
                {
                    _logger.LogWarning(ex, "数据库查询失败（第 {Attempt}/{Max} 次），将重试", attempt, maxRetries);
                }
                else
                {
                    _logger.LogError(ex, "数据库查询失败（已重试 {Retries} 次）: {Sql}", 
                        maxRetries - 1, sql);
                }
            }
        }
        
        // 所有重试都失败了
        result.Success = false;
        result.Error = $"查询失败（已重试{maxRetries - 1}次）: {lastException?.Message}";
        result.ExecutionTimeMs = stopwatch.ElapsedMilliseconds;
        
        return result;
    }

    public async Task<bool> TestConnectionAsync(string databaseName, CancellationToken cancellationToken = default)
    {
        try
        {
            var connection = GetConnectionInfo(databaseName);
            using var conn = CreateConnection(connection);
            
            conn.Open();
            
            // Test with simple query
            using var cmd = conn.CreateCommand();
            cmd.CommandText = GetTestQuery(connection.Provider);
            cmd.ExecuteScalar();
            
            return true;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to test connection to database: {Database}", databaseName);
            return false;
        }
    }

    private Configuration.ConnectionInfo GetConnectionInfo(string? databaseName)
    {
        if (string.IsNullOrEmpty(databaseName))
        {
            return _config.Connections.FirstOrDefault(c => c.IsDefault) ?? _config.DefaultConnection;
        }

        return _config.Connections.FirstOrDefault(c => 
            string.Equals(c.Name, databaseName, StringComparison.OrdinalIgnoreCase)) 
            ?? throw new ArgumentException($"Database '{databaseName}' not found");
    }

    private IDbConnection CreateConnection(Configuration.ConnectionInfo connectionInfo)
    {
        return connectionInfo.Provider.ToLower() switch
        {
            "sqlserver" => new SqlConnection(connectionInfo.ConnectionString),
            "postgresql" => new NpgsqlConnection(connectionInfo.ConnectionString),
            "mysql" => new MySqlConnection(connectionInfo.ConnectionString),
            _ => throw new NotSupportedException($"Provider {connectionInfo.Provider} not supported")
        };
    }

    private string GetTestQuery(string provider)
    {
        return provider.ToLower() switch
        {
            "sqlserver" => "SELECT 1",
            "postgresql" => "SELECT 1",
            "mysql" => "SELECT 1",
            _ => "SELECT 1"
        };
    }
}
